#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

ADS_REGION_ADS_REPORT_MYSQL="
USE jtp_warehouse;
INSERT OVERWRITE TABLE ads_region_ads_report
SELECT dt, province, city, is_invalid_traffic, click_count, impression_count FROM ads_region_ads_report
UNION
SELECT
    '2024-10-01' AS dt
    ,if(client_province = '0','未知',client_province) AS province
    ,if(client_city = '0','未知',client_city) AS city
    ,if(is_invalid_traffic, '异常流量', '正常流量') AS is_invalid_traffic
    ,count(if(event_type = 'click',ad_id,NULL)) AS click_count
    ,count(ad_id) AS impression_count
FROM jtp_warehouse.dwd_ads_event_log_lnc
WHERE dt = '${data_date}'
AND event_type IN ('click','impression')
GROUP BY client_province
      ,client_city
      ,is_invalid_traffic
;
"

ADS_PLATFORM_ADS_REPORT_MYSQL="
USE jtp_warehouse;
INSERT OVERWRITE TABLE jtp_warehouse.ads_platform_ads_report
SELECT dt, platform_id, platform_name_zh, is_invalid_traffic, click_count, impression_count FROM jtp_warehouse.ads_platform_ads_report
UNION
SELECT
    '2024-10-01' AS dt
     ,if(platform_id = '0','未知',platform_id) AS platform_id
     ,if(platform_name_zn = '0','未知',platform_name_zn) AS platform_name_zn
     ,if(is_invalid_traffic, '异常流量', '正常流量') AS is_invalid_traffic
     ,count(if(event_type = 'click',ad_id,NULL)) AS click_count
     ,count(ad_id) AS impression_count
FROM jtp_warehouse.dwd_ads_event_log_lnc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY platform_id
       ,platform_name_zn
       ,is_invalid_traffic
;
"


ADS_OS_ADS_REPORT_MYSQL="
INSERT OVERWRITE TABLE jtp_warehouse.ads_os_ads_report
SELECT dt, client_os_type, is_invalid_traffic, click_count, impression_count FROM jtp_warehouse.ads_os_ads_report
UNION
SELECT
    '2024-10-01' AS dt
     ,if(client_os_type = '0','未知',client_os_type) AS client_os_type
     ,if(is_invalid_traffic, '异常流量', '正常流量') AS is_invalid_traffic
     ,count(if(event_type = 'click',ad_id,NULL)) AS click_count
     ,count(ad_id) AS impression_count
FROM jtp_warehouse.dwd_ads_event_log_lnc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY client_os_type
       ,is_invalid_traffic
;
"


ADS_HOUR_ADS_REPORT_MYSQL="
INSERT OVERWRITE TABLE jtp_warehouse.ads_hour_ads_report
SELECT dt, ad_id, ads_name, hour_str, is_invalid_traffic, click_count, impression_count FROM jtp_warehouse.ads_hour_ads_report
UNION
SELECT
    '2024-10-01' AS dt
     ,ad_id,ads_name
     ,hour(from_unixtime(event_time/1000)) AS hour_str
     ,if(is_invalid_traffic, '异常流量', '正常流量') AS is_invalid_traffic
     ,count(if(event_type = 'click',ad_id,NULL)) AS click_count
     ,count(ad_id) AS impression_count
FROM jtp_warehouse.dwd_ads_event_log_lnc
WHERE dt = '${data_date}'
  AND event_type IN ('click','impression')
GROUP BY ad_id,ads_name
       ,hour(from_unixtime(event_time/1000))
       ,is_invalid_traffic
;
"


/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e  \
"${ADS_REGION_ADS_REPORT_MYSQL}${ADS_PLATFORM_ADS_REPORT_MYSQL}${ADS_OS_ADS_REPORT_MYSQL}
${ADS_HOUR_ADS_REPORT_MYSQL}"